package com.oa.modules.approvalsec1.controller;

import com.oa.common.utils.R;
import com.oa.modules.approvalsec1.entity.*;
import com.oa.modules.approvalsec1.service.*;
import io.swagger.annotations.ApiOperation;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 导出（立项、施工许可、工程建设、竣工验收、并行推进）
 */
@RestController
@RequestMapping("import")
public class importExcelController {

    @Autowired
    private ProjectApprovalService projectApprovalService;

    @Autowired
    private ConstructPermitService constructPermitService;

    @Autowired
    private EngineerConstructService engineerConstructService;

    @Autowired
    private CompletAcceptService completAcceptService;

    @Autowired
    private PropulsionService propulsionService;

    /**
     * excel文件导入
     */
    @PostMapping(value = "/importExcel")
    @ResponseBody
    @ApiOperation(value = "excel文件导入")
    public R uploadPicture(@RequestParam("file") MultipartFile file, HttpServletRequest servletRequest) throws Exception {

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Map<String, Object> map = new HashMap<>();
        String fileName = file.getOriginalFilename();

        try {
            Workbook wb = null;
            String excelVersion = null;
            if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
                wb = new XSSFWorkbook(file.getInputStream());
                excelVersion = "2007";
            } else if (fileName.matches("^.+\\.(?i)(xls)$")) {
                wb = new HSSFWorkbook(file.getInputStream());
                excelVersion = "2003";
            }
            for(int count = 0; count<5; count++){
                // 得到第一个shell
                Sheet sheet = wb.getSheetAt(count);
                if(count == 0){
                    //立项
                    map = firstSheet(sheet,map);
                    List<ProjectApproval> list = (List<ProjectApproval>) map.get("projectApproval");
                    for (int row = 0; row < list.size(); row++) {
                        List<ProjectApproval> list2 = projectApprovalService.queryAll(list.get(row));
                        if (list2.size() == 0) {
                            projectApprovalService.insert(list.get(row));
                        }else{
                            for (int row2 = 0; row2 < list2.size(); row2++) {
                                //demo1是接收者，后者是赋值者
                                BeanUtils.copyProperties(list2.get(row2), list.get(row));
                                projectApprovalService.update(list2.get(row2));
                            }
                        }
                    }
                }else if(count == 1){
                    //施工许可
                    map = secondSheet(sheet,map);
                    List<ConstructPermit> list = (List<ConstructPermit>) map.get("constructPermit");
                    for (int row = 0; row < list.size(); row++) {
                        List<ConstructPermit> list2 = constructPermitService.queryAll(list.get(row));
                        if (list2.size() == 0) {
                            constructPermitService.insert(list.get(row));
                        }else{
                            for (int row2 = 0; row2 < list2.size(); row2++) {
                                //demo1是接收者，后者是赋值者
                                BeanUtils.copyProperties(list2.get(row2), list.get(row));
                                constructPermitService.update(list2.get(row2));
                            }
                        }
                    }
                }else if(count == 2){
                    //工程建设
                    map = thirdSheet(sheet,map);
                    List<EngineerConstruct> list = (List<EngineerConstruct>) map.get("engineerConstruct");
                    for (int row = 0; row < list.size(); row++) {
                        List<EngineerConstruct> list2 = engineerConstructService.queryAll(list.get(row));
                        if (list2.size() == 0) {
                            engineerConstructService.insert(list.get(row));
                        }else{
                            for (int row2 = 0; row2 < list2.size(); row2++) {
                                //demo1是接收者，后者是赋值者
                                BeanUtils.copyProperties(list2.get(row2), list.get(row));
                                engineerConstructService.update(list2.get(row2));
                            }
                        }
                    }
                }else if(count == 3){
                    //竣工验收
                    map = fourthSheet(sheet,map);
                    List<CompletAccept> list = (List<CompletAccept>) map.get("completAccept");
                    for (int row = 0; row < list.size(); row++) {
                        List<CompletAccept> list2 = completAcceptService.queryAll(list.get(row));
                        if (list2.size() == 0) {
                            completAcceptService.insert(list.get(row));
                        }else{
                            for (int row2 = 0; row2 < list2.size(); row2++) {
                                //demo1是接收者，后者是赋值者
                                BeanUtils.copyProperties(list2.get(row2), list.get(row));
                                completAcceptService.update(list2.get(row2));
                            }
                        }
                    }
                }else if(count == 4){
                    //并行推进
                    map = fifthSheet(sheet,map);
                    List<Propulsion> list = (List<Propulsion>) map.get("propulsion");
                    for (int row = 0; row < list.size(); row++) {
                        List<Propulsion> list2 = propulsionService.queryAll(list.get(row));
                        if (list2.size() == 0) {
                            propulsionService.insert(list.get(row));
                        }else{
                            for (int row2 = 0; row2 < list2.size(); row2++) {
                                //demo1是接收者，后者是赋值者
                                BeanUtils.copyProperties(list2.get(row2), list.get(row));
                                propulsionService.update(list2.get(row2));
                            }
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            return R.error();
        }
        return R.ok();
    }


    public static Map<String,Object> firstSheet(Sheet sheet,Map<String,Object> map){
        List<ProjectApproval> list = new ArrayList<>();
        //获取sheet中第一行行号
        int firstRowNum = sheet.getFirstRowNum();
        //获取sheet中最后一行行号
        int lastRowNum = sheet.getLastRowNum();

        //是否成功循环完成
        boolean isSuccess = true;
        //若出现错误break，i即为首先出现错误的行(出现错误循环直接结束) i即为出错行的序号
        int i = 0;
        try {
            //循环插入数据
            for (i = firstRowNum + 1; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                ProjectApproval projectApproval = new ProjectApproval();
                //获取业务名称
                Cell businessNameCell = row.getCell(0);
                if (businessNameCell != null) {
                    businessNameCell.setCellType(Cell.CELL_TYPE_STRING);
                    if (businessNameCell.getStringCellValue() != null && businessNameCell.getStringCellValue().trim().length() != 0) {
                        projectApproval.setBusinessName(businessNameCell.getStringCellValue().trim());
                    } else {
                        break;
                    }
                } else {
                    break;
                }
                //获取单位名称
                Cell unitNameCell = row.getCell(1);
                if (unitNameCell != null) {
                    unitNameCell.setCellType(Cell.CELL_TYPE_STRING);
                    if (unitNameCell.getStringCellValue() != null && unitNameCell.getStringCellValue().trim().length() != 0) {
                        projectApproval.setUnitName(unitNameCell.getStringCellValue().trim());
                    }
                }
                //获取证件类型
                Cell certificateTypeCell = row.getCell(2);
                if (certificateTypeCell != null) {
                    certificateTypeCell.setCellType(Cell.CELL_TYPE_STRING);
                    if (certificateTypeCell.getStringCellValue() != null && certificateTypeCell.getStringCellValue().trim().length() != 0) {
                        projectApproval.setCertificateType(certificateTypeCell.getStringCellValue().trim());
                    }
                }
                //获取证件代码
                Cell certificateCodeCell = row.getCell(3);
                if (certificateCodeCell != null) {
                    certificateCodeCell.setCellType(Cell.CELL_TYPE_STRING);
                    if (certificateCodeCell.getStringCellValue() != null && certificateCodeCell.getStringCellValue().trim().length() != 0) {
                        projectApproval.setCertificateCode(certificateCodeCell.getStringCellValue().trim());
                    }
                }
                //获取项目名称
                Cell projectNameCell = row.getCell(4);
                if (projectNameCell != null) {
                    projectNameCell.setCellType(Cell.CELL_TYPE_STRING);
                    if (projectNameCell.getStringCellValue() != null && projectNameCell.getStringCellValue().trim().length() != 0) {
                        projectApproval.setProjectName(projectNameCell.getStringCellValue().trim());
                    }
                }
                //获取项目类别
                Cell projectTypeCell = row.getCell(5);
                if (projectTypeCell != null) {
                    projectTypeCell.setCellType(Cell.CELL_TYPE_STRING);
                    if (projectTypeCell.getStringCellValue() != null && projectTypeCell.getStringCellValue().trim().length() != 0) {
                        projectApproval.setProjectType(projectTypeCell.getStringCellValue().trim());
                    }
                }
                //获取建设性质
                Cell constructNatureCell = row.getCell(6);
                if (constructNatureCell != null) {
                    constructNatureCell.setCellType(Cell.CELL_TYPE_STRING);
                    if (constructNatureCell.getStringCellValue() != null && constructNatureCell.getStringCellValue().trim().length() != 0) {
                        projectApproval.setConstructNature(constructNatureCell.getStringCellValue().trim());
                    }
                }
                //获取项目总投资
                Cell totalInvestCell = row.getCell(7);
                if (totalInvestCell != null) {
                    totalInvestCell.setCellType(Cell.CELL_TYPE_STRING);
                    if (totalInvestCell.getStringCellValue() != null && totalInvestCell.getStringCellValue().trim().length() != 0) {
                        projectApproval.setTotalInvest(Double.valueOf(totalInvestCell.getStringCellValue().trim()));
                    }
                }

                list.add(projectApproval);
            }
        } catch (Exception e) {
            e.printStackTrace();

        }
        map.put("projectApproval", list);
        return map;
    }

    public static Map<String,Object> secondSheet(Sheet sheet,Map<String,Object> map){
        List<ConstructPermit> list = new ArrayList<>();
        //获取sheet中第一行行号
        int firstRowNum = sheet.getFirstRowNum();
        //获取sheet中最后一行行号
        int lastRowNum = sheet.getLastRowNum();

        //是否成功循环完成
        boolean isSuccess = true;
        //若出现错误break，i即为首先出现错误的行(出现错误循环直接结束) i即为出错行的序号
        int i = 0;
        try {
            //循环插入数据
            for (i = firstRowNum + 1; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                ConstructPermit constructPermit = new ConstructPermit();
                //获取业务名称
                Cell businessNameCell = row.getCell(0);
                if (businessNameCell != null) {
                    businessNameCell.setCellType(Cell.CELL_TYPE_STRING);
                    if (businessNameCell.getStringCellValue() != null && businessNameCell.getStringCellValue().trim().length() != 0) {
                        constructPermit.setBusinessName(businessNameCell.getStringCellValue().trim());
                    } else {
                        break;
                    }
                } else {
                    break;
                }
                //获取工程名称
                Cell engineerName = row.getCell(1);
                if (engineerName != null) {
                    engineerName.setCellType(Cell.CELL_TYPE_STRING);
                    if (engineerName.getStringCellValue() != null && engineerName.getStringCellValue().trim().length() != 0) {
                        constructPermit.setEngineerName(engineerName.getStringCellValue().trim());
                    }
                }
                //获取投资来源
                Cell incestSource = row.getCell(2);
                if (incestSource != null) {
                    incestSource.setCellType(Cell.CELL_TYPE_STRING);
                    if (incestSource.getStringCellValue() != null && incestSource.getStringCellValue().trim().length() != 0) {
                        constructPermit.setIncestSource(incestSource.getStringCellValue().trim());
                    }
                }
                //获取建设地点
                Cell location = row.getCell(3);
                if (location != null) {
                    location.setCellType(Cell.CELL_TYPE_STRING);
                    if (location.getStringCellValue() != null && location.getStringCellValue().trim().length() != 0) {
                        constructPermit.setLocation(location.getStringCellValue().trim());
                    }
                }
                //获取建设性质
                Cell constructNature = row.getCell(4);
                if (constructNature != null) {
                    constructNature.setCellType(Cell.CELL_TYPE_STRING);
                    if (constructNature.getStringCellValue() != null && constructNature.getStringCellValue().trim().length() != 0) {
                        constructPermit.setConstructNature(constructNature.getStringCellValue().trim());
                    }
                }
                //获取建设单位
                Cell unitsName = row.getCell(5);
                if (unitsName != null) {
                    unitsName.setCellType(Cell.CELL_TYPE_STRING);
                    if (unitsName.getStringCellValue() != null && unitsName.getStringCellValue().trim().length() != 0) {
                        constructPermit.setUnitsName(unitsName.getStringCellValue().trim());
                    }
                }
                //获取项目负责人
                Cell projectLeader = row.getCell(6);
                if (projectLeader != null) {
                    projectLeader.setCellType(Cell.CELL_TYPE_STRING);
                    if (projectLeader.getStringCellValue() != null && projectLeader.getStringCellValue().trim().length() != 0) {
                        constructPermit.setProjectLeader(projectLeader.getStringCellValue().trim());
                    }
                }
                //获取社会信用代码
                Cell socialCode = row.getCell(7);
                if (socialCode != null) {
                    socialCode.setCellType(Cell.CELL_TYPE_STRING);
                    if (socialCode.getStringCellValue() != null && socialCode.getStringCellValue().trim().length() != 0) {
                        constructPermit.setSocialCode(socialCode.getStringCellValue().trim());
                    }
                }
                //获取联系电话
                Cell leaderPhone = row.getCell(8);
                if (leaderPhone != null) {
                    leaderPhone.setCellType(Cell.CELL_TYPE_STRING);
                    if (leaderPhone.getStringCellValue() != null && leaderPhone.getStringCellValue().trim().length() != 0) {
                        constructPermit.setLeaderPhone(leaderPhone.getStringCellValue().trim());
                    }
                }
                //获取身份证号
                Cell leaderIdcard = row.getCell(9);
                if (leaderIdcard != null) {
                    leaderIdcard.setCellType(Cell.CELL_TYPE_STRING);
                    if (leaderIdcard.getStringCellValue() != null && leaderIdcard.getStringCellValue().trim().length() != 0) {
                        constructPermit.setLeaderIdcard(leaderIdcard.getStringCellValue().trim());
                    }
                }

                list.add(constructPermit);
            }
        } catch (Exception e) {
            e.printStackTrace();

        }
        map.put("constructPermit", list);
        return map;
    }

    public static Map<String,Object> thirdSheet(Sheet sheet,Map<String,Object> map){
        List<EngineerConstruct> list = new ArrayList<>();
        //获取sheet中第一行行号
        int firstRowNum = sheet.getFirstRowNum();
        //获取sheet中最后一行行号
        int lastRowNum = sheet.getLastRowNum();

        //是否成功循环完成
        boolean isSuccess = true;
        //若出现错误break，i即为首先出现错误的行(出现错误循环直接结束) i即为出错行的序号
        int i = 0;
        try {
            //循环插入数据
            for (i = firstRowNum + 1; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                EngineerConstruct engineerConstruct = new EngineerConstruct();
                //获取业务名称
                Cell businessNameCell = row.getCell(0);
                if (businessNameCell != null) {
                    businessNameCell.setCellType(Cell.CELL_TYPE_STRING);
                    if (businessNameCell.getStringCellValue() != null && businessNameCell.getStringCellValue().trim().length() != 0) {
                        engineerConstruct.setBusinessName(businessNameCell.getStringCellValue().trim());
                    } else {
                        break;
                    }
                } else {
                    break;
                }
                //工程名称
                Cell engineerName = row.getCell(1);
                if (engineerName != null) {
                    engineerName.setCellType(Cell.CELL_TYPE_STRING);
                    if (engineerName.getStringCellValue() != null && engineerName.getStringCellValue().trim().length() != 0) {
                        engineerConstruct.setEngineerName(engineerName.getStringCellValue().trim());
                    }
                }
                //证件类型
                Cell certificateType = row.getCell(2);
                if (certificateType != null) {
                    certificateType.setCellType(Cell.CELL_TYPE_STRING);
                    if (certificateType.getStringCellValue() != null && certificateType.getStringCellValue().trim().length() != 0) {
                        engineerConstruct.setCertificateType(certificateType.getStringCellValue().trim());
                    }
                }
                //证件代码
                Cell certificateCodeCell = row.getCell(3);
                if (certificateCodeCell != null) {
                    certificateCodeCell.setCellType(Cell.CELL_TYPE_STRING);
                    if (certificateCodeCell.getStringCellValue() != null && certificateCodeCell.getStringCellValue().trim().length() != 0) {
                        engineerConstruct.setCertificateCode(certificateCodeCell.getStringCellValue().trim());
                    }
                }
                //证件代码
                Cell projectNameCell = row.getCell(4);
                if (projectNameCell != null) {
                    projectNameCell.setCellType(Cell.CELL_TYPE_STRING);
                    if (projectNameCell.getStringCellValue() != null && projectNameCell.getStringCellValue().trim().length() != 0) {
                        engineerConstruct.setProjectName(projectNameCell.getStringCellValue().trim());
                    }
                }
                //项目类型
                Cell projectTypeCell = row.getCell(5);
                if (projectTypeCell != null) {
                    projectTypeCell.setCellType(Cell.CELL_TYPE_STRING);
                    if (projectTypeCell.getStringCellValue() != null && projectTypeCell.getStringCellValue().trim().length() != 0) {
                        engineerConstruct.setProjectType(projectTypeCell.getStringCellValue().trim());
                    }
                }
                //建设性质
                Cell constructNatureCell = row.getCell(6);
                if (constructNatureCell != null) {
                    constructNatureCell.setCellType(Cell.CELL_TYPE_STRING);
                    if (constructNatureCell.getStringCellValue() != null && constructNatureCell.getStringCellValue().trim().length() != 0) {
                        engineerConstruct.setConstructNature(constructNatureCell.getStringCellValue().trim());
                    }
                }
                //获取建设总投资
                Cell totalInvestCell = row.getCell(7);
                if (totalInvestCell != null) {
                    totalInvestCell.setCellType(Cell.CELL_TYPE_STRING);
                    if (totalInvestCell.getStringCellValue() != null && totalInvestCell.getStringCellValue().trim().length() != 0) {
                        engineerConstruct.setTotalInvest(Double.valueOf(totalInvestCell.getStringCellValue().trim()));
                    }
                }

                list.add(engineerConstruct);
            }
        } catch (Exception e) {
            e.printStackTrace();

        }
        map.put("engineerConstruct", list);
        return map;
    }

    public static Map<String,Object> fourthSheet(Sheet sheet,Map<String,Object> map){
        List<CompletAccept> list = new ArrayList<>();
        //获取sheet中第一行行号
        int firstRowNum = sheet.getFirstRowNum();
        //获取sheet中最后一行行号
        int lastRowNum = sheet.getLastRowNum();

        //是否成功循环完成
        boolean isSuccess = true;
        //若出现错误break，i即为首先出现错误的行(出现错误循环直接结束) i即为出错行的序号
        int i = 0;
        try {
            //循环插入数据
            for (i = firstRowNum + 1; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                CompletAccept completAccept = new CompletAccept();
                //获取业务名称
                Cell businessNameCell = row.getCell(0);
                if (businessNameCell != null) {
                    businessNameCell.setCellType(Cell.CELL_TYPE_STRING);
                    if (businessNameCell.getStringCellValue() != null && businessNameCell.getStringCellValue().trim().length() != 0) {
                        completAccept.setBusinessName(businessNameCell.getStringCellValue().trim());
                    } else {
                        break;
                    }
                } else {
                    break;
                }
                //工程名称
                Cell engineerName = row.getCell(1);
                if (engineerName != null) {
                    engineerName.setCellType(Cell.CELL_TYPE_STRING);
                    if (engineerName.getStringCellValue() != null && engineerName.getStringCellValue().trim().length() != 0) {
                        completAccept.setEngineerName(engineerName.getStringCellValue().trim());
                    }
                }
                //项目代码
                Cell projectCode = row.getCell(2);
                if (projectCode != null) {
                    projectCode.setCellType(Cell.CELL_TYPE_STRING);
                    if (projectCode.getStringCellValue() != null && projectCode.getStringCellValue().trim().length() != 0) {
                        completAccept.setProjectCode(projectCode.getStringCellValue().trim());
                    }
                }
                //统一社会代码
                Cell socialCode = row.getCell(3);
                if (socialCode != null) {
                    socialCode.setCellType(Cell.CELL_TYPE_STRING);
                    if (socialCode.getStringCellValue() != null && socialCode.getStringCellValue().trim().length() != 0) {
                        completAccept.setSocialCode(socialCode.getStringCellValue().trim());
                    }
                }
                //项目负责人
                Cell projectLeader = row.getCell(4);
                if (projectLeader != null) {
                    projectLeader.setCellType(Cell.CELL_TYPE_STRING);
                    if (projectLeader.getStringCellValue() != null && projectLeader.getStringCellValue().trim().length() != 0) {
                        completAccept.setProjectLeader(projectLeader.getStringCellValue().trim());
                    }
                }
                //身份证号
                Cell leaderIdcard = row.getCell(5);
                if (leaderIdcard != null) {
                    leaderIdcard.setCellType(Cell.CELL_TYPE_STRING);
                    if (leaderIdcard.getStringCellValue() != null && leaderIdcard.getStringCellValue().trim().length() != 0) {
                        completAccept.setLeaderIdcard(leaderIdcard.getStringCellValue().trim());
                    }
                }
                //移动电话
                Cell leaderPhone = row.getCell(6);
                if (leaderPhone != null) {
                    leaderPhone.setCellType(Cell.CELL_TYPE_STRING);
                    if (leaderPhone.getStringCellValue() != null && leaderPhone.getStringCellValue().trim().length() != 0) {
                        completAccept.setLeaderPhone(leaderPhone.getStringCellValue().trim());
                    }
                }

                list.add(completAccept);
            }
        } catch (Exception e) {
            e.printStackTrace();

        }
        map.put("completAccept", list);
        return map;
    }

    public static Map<String,Object> fifthSheet(Sheet sheet,Map<String,Object> map){
        List<Propulsion> list = new ArrayList<>();
        //获取sheet中第一行行号
        int firstRowNum = sheet.getFirstRowNum();
        //获取sheet中最后一行行号
        int lastRowNum = sheet.getLastRowNum();

        //是否成功循环完成
        boolean isSuccess = true;
        //若出现错误break，i即为首先出现错误的行(出现错误循环直接结束) i即为出错行的序号
        int i = 0;
        try {
            //循环插入数据
            for (i = firstRowNum + 1; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                Propulsion propulsion = new Propulsion();
                //获取业务名称
                Cell businessNameCell = row.getCell(0);
                if (businessNameCell != null) {
                    businessNameCell.setCellType(Cell.CELL_TYPE_STRING);
                    if (businessNameCell.getStringCellValue() != null && businessNameCell.getStringCellValue().trim().length() != 0) {
                        propulsion.setBusinessName(businessNameCell.getStringCellValue().trim());
                    } else {
                        break;
                    }
                } else {
                    break;
                }
                //获取单位名称
                Cell unitsName = row.getCell(1);
                if (unitsName != null) {
                    unitsName.setCellType(Cell.CELL_TYPE_STRING);
                    if (unitsName.getStringCellValue() != null && unitsName.getStringCellValue().trim().length() != 0) {
                        propulsion.setUnitsName(unitsName.getStringCellValue().trim());
                    }
                }
                Cell certificateTypeCell = row.getCell(2); //获取证件类型
                if (certificateTypeCell != null) {
                    certificateTypeCell.setCellType(Cell.CELL_TYPE_STRING);
                    if (certificateTypeCell.getStringCellValue() != null && certificateTypeCell.getStringCellValue().trim().length() != 0) {
                        propulsion.setCertificateType(certificateTypeCell.getStringCellValue().trim());
                    }
                }
                Cell certificateCodeCell = row.getCell(3); //获取证件代码
                if (certificateCodeCell != null) {
                    certificateCodeCell.setCellType(Cell.CELL_TYPE_STRING);
                    if (certificateCodeCell.getStringCellValue() != null && certificateCodeCell.getStringCellValue().trim().length() != 0) {
                        propulsion.setCertificateCode(certificateCodeCell.getStringCellValue().trim());
                    }
                }
                Cell projectNameCell = row.getCell(4); //获取项目名称
                if (projectNameCell != null) {
                    projectNameCell.setCellType(Cell.CELL_TYPE_STRING);
                    if (projectNameCell.getStringCellValue() != null && projectNameCell.getStringCellValue().trim().length() != 0) {
                        propulsion.setProjectName(projectNameCell.getStringCellValue().trim());
                    }
                }
                Cell totalInvest = row.getCell(5); //获取项目总投资
                if (totalInvest != null) {
                    totalInvest.setCellType(Cell.CELL_TYPE_STRING);
                    if (totalInvest.getStringCellValue() != null && totalInvest.getStringCellValue().trim().length() != 0) {
                        propulsion.setTotalInvest(Double.valueOf(totalInvest.getStringCellValue().trim()));
                    }
                }

                list.add(propulsion);
            }
        } catch (Exception e) {
            e.printStackTrace();

        }
        map.put("propulsion", list);
        return map;
    }


    @RequestMapping("/downloadExcel")
    @ResponseBody
    public void downloadExcel(HttpServletResponse response, HttpServletRequest request) {
        try {
            //获取要下载的模板名称
            String fileName = "数据模板.xlsx";
            //通知客服文件的MIME类型
            response.setContentType("application/octet-stream;charset=utf-8");
            //设置要下载的文件的名称
            response.setHeader("Content-Disposition", "attachment;filename="
                    + new String(fileName.getBytes(), "iso-8859-1"));
            //获取文件的路径
            String filePath = "/opt/templates" + File.separator + fileName;
            FileInputStream input = new FileInputStream(filePath);
            OutputStream out = response.getOutputStream();
            byte[] b = new byte[2048];
            int len;
            while ((len = input.read(b)) != -1) {
                out.write(b, 0, len);
            }
            response.setHeader("Content-Length", String.valueOf(input.getChannel().size()));
            input.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}
